import pandas as pd
import argparse
class mathuntil:
    @staticmethod
    def check_filepath(a)->bool:
        print(a,'b')
        if not open(a):
            print("file path invidils")
            return False
        else:
            return True
    def str_diff(st1:str,st2:str,laterstr:str)->str:
        'st1 st2 :用于比较的字符串'
        'laterstr  :用于分割位号的符号'
        result=""
        a1=[]
        disc_tmp={}
        arr1 = st1.split(laterstr)
        arr2 = st2.split(laterstr)
        for item in arr1:
            disc_tmp['$'+item] = item
        for item in arr2:
            if '$'+item in disc_tmp:
                disc_tmp['$'+item] = '$'+item
            else:
                disc_tmp[item] = '$'+item
        arr1 = [key for key in disc_tmp.keys() if '$' not in key]
        arr1_str = '.'.join(arr1)
        arr2 = [val for val in disc_tmp.values() if '$'not in val]
        arr2_str = '.'.join(arr2)
        del disc_tmp
        if len(arr1_str)>0:
            result = "ADD: "+arr1_str
        if len(arr2_str)>0:
             result = result+'\n'+'DEL: '+arr2_str
        result = result.replace("  ",";")
        return result
class bom_compare:
    compare_results = []
    def __init__(self):
        sheet_name=pd.ExcelFile('io_bom1.xlsx').sheet_names[1]
        self.bom1 = pd.read_excel('io_bom1.xlsx',sheet_name=sheet_name)
        # 读取第二个BOM数据
        self.bom2 = pd.read_excel('io_bom2.xlsx',sheet_name=sheet_name)
        
    def data_clear(self):
        # 数据清洗和预处理部分
        # 检查并统一列名，确保关键列名一致
        if not self.bom1.columns.equals(self.bom2.columns):
            self.bom2 = self.bom2.rename(columns={
                '旧列名1': '新列名1',
                '旧列名2': '新列名2',
                # 根据实际情况补充其他列的重命名
            })

        # 处理缺失值，这里简单填充为空字符串，可根据实际调整
        self.bom1 = self.bom1.fillna('')
        self.bom2 = self.bom2.fillna('')

        # 去除Part Reference中的空格等空白字符，确保格式统一便于对比
        self.bom1['Part Reference'] = self.bom1['Part Reference'].str.strip()
        self.bom2['Part Reference'] = self.bom2['Part Reference'].str.strip()

        # 将Part Reference列中的数据转换为集合类型，方便后续对比操作(一个物料可能对应多个位号)
        self.bom1['Part Reference'] = self.bom1['Part Reference'].apply(lambda x: set(x.split(',')) if isinstance(x, str) else set())
        self.bom2['Part Reference'] = self.bom2['Part Reference'].apply(lambda x: set(x.split(',')) if isinstance(x, str) else set())

        # 用于存储比对结果的列表，每个元素是一个字典，对应一种变化情况
    def find_del_meterial(self):
        deleted_materials = self.bom1[~self.bom1['New Part Number'].isin(self.bom2['New Part Number'])]
        print(deleted_materials)

        for _, row in deleted_materials.iterrows():
            
            self.compare_results.append({
                '变化类型': '删除物料',
                'New Part Number': row['New Part Number'],
                'Part Reference': ', '.join(row['Part Reference']),
                'Quantity': row['Quantity']
            })
    def find_add_meterail(self):
        self.added_materials = self.bom2[~self.bom2['New Part Number'].isin(self.bom1['New Part Number'])]
        for _, row in self.added_materials.iterrows():
            self.compare_results.append({
                '变化类型': '添加新料',
                'New Part Number': row['New Part Number'],
                'Part Reference': ', '.join(row['Part Reference']),
                'Quantity': row['Quantity']
            })
    def find_change_meterial(self):
        common_codes = self.bom1[self.bom1['New Part Number'].isin(self.bom2['New Part Number'])]['New Part Number'].unique()
        # 遍历共同的New Part Number，对比位号和Quantity变化
        for code in common_codes:
            bom1_row = self.bom1[self.bom1['New Part Number'] == code].iloc[0]
            bom2_row = self.bom2[self.bom2['New Part Number'] == code].iloc[0]

            bom1_pcbs = bom1_row['Part Reference']
            bom2_pcbs = bom2_row['Part Reference']
            bom1_quantity = bom1_row['Quantity']
            bom2_quantity = bom2_row['Quantity']
            if(code == 82000011):
                print(bom1_row['New Part Number'],bom1_pcbs,bom1_quantity)
                print(bom2_row['New Part Number'],bom2_pcbs,bom2_quantity)

            if bom1_pcbs!= bom2_pcbs or bom1_quantity!= bom2_quantity:
                if bom1_row['New Part Number']==(bom2_row['New Part Number']) and (bom1_pcbs!= bom2_pcbs or bom1_quantity!= bom2_quantity):
                    self.compare_results.append({
                        '变化类型': 'BOM料号不变,修改位号和Quantity',
                        'New Part Number': code,
                        'Part Reference(bom1)': ', '.join(bom1_pcbs),
                        'Part Reference(bom2)': ', '.join(bom2_pcbs),
                        'Quantity(bom1)': bom1_quantity,
                        'Quantity(bom2)': bom2_quantity
                    })
                    #diff_list.write(" ".join(compare_results[-1:]))
    def find_partRef(self):            
        common_codes = self.bom1[self.bom1['New Part Number'].isin(self.bom2['New Part Number'])]['New Part Number'].unique()
        # 遍历共同的New Part Number，对比位号和Quantity变化
        for code in common_codes:
            bom1_row = self.bom1[self.bom1['New Part Number'] == code].iloc[0]
            bom2_row = self.bom2[self.bom2['New Part Number'] == code].iloc[0]

            bom1_pcbs = bom1_row['Part Reference']
            bom2_pcbs = bom2_row['Part Reference']
            bom1_quantity = bom1_row['Quantity']
            bom2_quantity = bom2_row['Quantity']
            # if(code == 82000011):
            #     print(bom1_row['New Part Number'],bom1_pcbs,bom1_quantity)
            #     print(bom2_row['New Part Number'],bom2_pcbs,bom2_quantity)

            if bom1_pcbs!= bom2_pcbs or bom1_quantity!= bom2_quantity:
                if bom1_row['New Part Number']==(bom2_row['New Part Number']) or (bom1_pcbs!= bom2_pcbs or bom1_quantity!= bom2_quantity):
                    self.compare_results.append({
                        '变化类型': '替换物料,位号和Quantity不变',
                        'New Part Number(bom1)': code,
                        'New Part Number(bom2)': code,
                        'Part Reference': ', '.join(bom1_pcbs),
                        'Quantity': bom1_quantity
                    })
    def tets1(self):
        # 找出删除物料
        deleted_parts = self.bom1[~self.bom1['New Part Number'].isin(self.bom2['New Part Number'])]
        # 找出添加物料
        added_parts = self.bom2[~self.bom2['New Part Number'].isin(self.bom1['New Part Number'])]
        replaced_parts = []
        for _, row_deleted in deleted_parts.iterrows():
            for _, row_added in added_parts.iterrows():
                if row_deleted['Part Reference'] == row_added['Part Reference']:
                    
                    
                    #self.compare_results.remove(order_emement)
                    replaced_parts.append({
                        '变化类型': '替换物料',
                        'New Part Number(bom1)': row_deleted['New Part Number'],
                        'New Part Number(bom2)': row_added['New Part Number'],
                        'Part Reference': row_deleted['Part Reference']
                    })

                    # order_element=[element for element in self.compare_results if element['New Part Number']== row_deleted['New Part Number']]
                    # #self.compare_results.remove(order_element)
                    # print(order_element,'\n')
                    # del order_element
                    
        self.compare_results=self.compare_results+replaced_parts
        print(replaced_parts)
    def output_diff_result(self):
        compare_df = pd.DataFrame(self.compare_results)

        # 输出对比结果，将包含详细比对情况的数据框保存到Excel文件
        with pd.ExcelWriter('bom比对结果.xlsx') as writer:
            compare_df.to_excel(writer, sheet_name='比对结果', index=False)
    def PartRefence_compare(bom1:str,bom2:str):
        return OSError
    def compare(self):
        self.data_clear()
        self.find_add_meterail()
        self.find_del_meterial()
        #self.find_partRef()
        self.find_change_meterial()
        self.tets1()
        self.output_diff_result()
if __name__ == "__main__":
    a1=mathuntil.str_diff("c1 c2 c3","c1 c4"," ")
    print(a1)


    print('输入bom的路径')
    parser = argparse.ArgumentParser(description='bom diff tool')
    parser.add_argument('old_bom_file', metavar='N', type=str, nargs='+',
                        help='old bom path')
    parser.add_argument('new_bom_file', metavar='N', type=str, nargs='+',
                        help='old bom path')
    # args=parser.parse_args()
    # print(args)
    # if mathuntil.check_filepath(args.old_bom_file[0]):
    #     exit()

    bom_compare_hander=bom_compare()
    bom_compare_hander.compare()
    print('bom compare success')


    
    